﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.IO;
using System.Net.Mime;


namespace CM_Classes
{
    public class User
    {
        String connectionString = "Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";

        private int empID;
        public int EmpID
        {
            get { return empID; }
            set { empID = value; }
        }

        private string firstName;
        public string FirstName
        {
            get { return firstName; }
            set { firstName = value; }
        }

        private string lastName;
        public string LastName
        {
            get { return lastName; }
            set { lastName = value; }
        }

        private string userName;
        public string UserName
        {
            get { return userName; }
            set { userName = value; }
        }

        private string password;
        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        private byte? photoID;
        public byte? PhotoID
        {
            get { return photoID; }
            set { photoID = value; }
        }

        private bool userDisabled;
        public bool UserDisabled
        {
            get { return userDisabled; }
            set { userDisabled = value; }
        }

        private int groupID;
        public int GroupID
        {
            get { return groupID; }
            set { groupID = value; }
        }



        public List<User> GetUsers()
        {

            List<User> userList = new List<User>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string queryString = "Select *  from  [ConfigurationManager].[dbo].[User]";


                try
                {
                    connection.Open();

                    SqlCommand command = new SqlCommand(queryString, connection);

                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        User aUser = new User();

                        aUser.empID = reader.GetInt32(0);
                        aUser.firstName = reader.GetString(1);
                        aUser.lastName = reader.GetString(2);
                        aUser.userName = reader.GetString(3);
                        aUser.password = reader.GetString(4);
                        aUser.userDisabled = reader.GetBoolean(5);
                        //aUser.photoID = reader.GetByte(6);

                        userList.Add(aUser);
                    }

                }

                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

                return userList;
            }
        }

        public string AddUser(int ei, string fn, string ln, string un, string pw, bool udis)
        {

            SqlConnection connection = new SqlConnection(connectionString);

            string result = "";
            try
            {
                using (SqlCommand InsertCommand = new SqlCommand("INSERT INTO [ConfigurationManager].[dbo].[User] VALUES(@EmpID, @FirstName, @LastName, @UserName, @PassWord, @UserDisabled)", connection))
                {
                    InsertCommand.Parameters.AddWithValue("@EmpID", ei);
                    InsertCommand.Parameters.AddWithValue("@FirstName", fn);
                    InsertCommand.Parameters.AddWithValue("@LastName", ln);
                    InsertCommand.Parameters.AddWithValue("@UserName", un);
                    InsertCommand.Parameters.AddWithValue("@PassWord", pw);
                    InsertCommand.Parameters.AddWithValue("@UserDisabled", udis);


                    connection.Open();
                    InsertCommand.ExecuteNonQuery();
                    result = un;
                }
            }
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            return result;
        }

        //public string AddUser()
        //{
        //    string imgLocation = "";
        //    string results = "";
        //    string addUser = string.Empty;
        //    string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";

        //    using (SqlConnection conn = new SqlConnection(connectionString))
        //    {
        //        try
        //        {
        //            conn.Open();
        //            var addUserSP = "AddUser";
        //            using (SqlCommand addUserCommand = new SqlCommand(addUserSP, conn))
        //            {
        //                addUserCommand.CommandType = CommandType.StoredProcedure;
        //                addUserCommand.Parameters.AddWithValue("@EmpID", this.EmpID);
        //                addUserCommand.Parameters.AddWithValue("@FirstName", this.FirstName);
        //                addUserCommand.Parameters.AddWithValue("@LastName", this.LastName);
        //                addUserCommand.Parameters.AddWithValue("@UserName", this.UserName);
        //                addUserCommand.Parameters.AddWithValue("@Password", this.Password);
        //                addUserCommand.Parameters.AddWithValue("@UserDisabled", this.UserDisabled);

        //                if (PhotoID != null) //not sure about the object img; should be PhotoID?
        //                {
        //                    byte[] img = null;
        //                    FileStream fs = new FileStream(imgLocation, FileMode.Open, FileAccess.Read);
        //                    BinaryReader br = new BinaryReader(fs);
        //                    img = br.ReadBytes((int)fs.Length);
        //                    addUserCommand.Parameters.Add(new SqlParameter("@PhotoID", this.PhotoID));
        //                }
        //                else
        //                {
        //                    PhotoID = null;
        //                }

        //                int x = addUserCommand.ExecuteNonQuery();

        //                //LogTransaction(int empID, string appmsg, string exceptmsg, string severity)

        //                results = FirstName + " " + LastName + " has been added.";
        //            }
        //        }
        //        catch (SqlException sqlEx)
        //        {
        //            results = sqlEx.Message;
        //        }
        //        catch (Exception ex)
        //        {
        //            results = ex.Message;
        //        }
        //    }

        //    return results;
        //}

        public List<User> FindUserByID()
        {
            //string imgLocation = "";
            string results = "";
            User user = new User();
            string findUser = string.Empty;
            string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";
            List<User> User = new List<User>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    var addUserSP = "FindUserByID";
                    using (SqlCommand findUserCommand = new SqlCommand(addUserSP, conn))
                    {
                        findUserCommand.CommandType = CommandType.StoredProcedure;
                        findUserCommand.Parameters.AddWithValue("@EmpID", this.EmpID);

                        SqlDataReader reader = findUserCommand.ExecuteReader();
                        reader.Read();
                        if (reader.HasRows)
                        {
                            user.FirstName = reader[0].ToString();
                            user.LastName = reader[1].ToString();
                            user.UserName = reader[2].ToString();
                            user.Password = reader[3].ToString();
                            byte[] PhotoID = (byte[])(reader[4]);
                            user.UserDisabled = Convert.ToBoolean(reader[5].ToString());

                            if (PhotoID == null)
                                user.PhotoID = null;
                            else
                            {
                                //   Memorystream ms = new Memorystream(PhotoID); //??????
                                //   user.PhotoID = Image.FromStream(ms);  //???????
                            }

                        }
                        else
                        {
                            results = "Employee not found.";
                        }

                        //LogTransaction(int empID, string appmsg, string exceptmsg, string severity)

                    }
                }
                catch (SqlException sqlEx)
                {
                    results = sqlEx.Message;
                }
                catch (Exception ex)
                {
                    results = ex.Message;
                }
            }

            return User;
        }


        public List<User> FindUserByLastName()
        {
            //string imgLocation = "";
            string results = "";
            User user = new User();
            string findUser = string.Empty;
            string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";
            List<User> User = new List<User>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    var addUserSP = "FindUserByLastName";
                    using (SqlCommand findUserCommand = new SqlCommand(addUserSP, conn))
                    {
                        findUserCommand.CommandType = CommandType.StoredProcedure;
                        findUserCommand.Parameters.AddWithValue("@LastName", this.LastName);

                        SqlDataReader reader = findUserCommand.ExecuteReader();
                        reader.Read();
                        if (reader.HasRows)
                        {
                            user.FirstName = reader[0].ToString();
                            user.LastName = reader[1].ToString();
                            user.UserName = reader[2].ToString();
                            user.Password = reader[3].ToString();
                            //byte[] PhotoID = (byte[])(reader[4]);
                            user.UserDisabled = Convert.ToBoolean(reader[5].ToString());

                            //if (img == null)
                            //    imgbxPhotoID.Image = null;
                            //else
                            //{
                            //    Memorystream ms = new Memorystream(img);
                            //    imgbxPhotoID.Image = Image.FromStream(ms);
                            //}

                        }
                        else
                        {
                            results = "Employee not found.";
                        }

                    }
                }
                catch (SqlException sqlEx)
                {
                    results = sqlEx.Message;
                }
                catch (Exception ex)
                {
                    results = ex.Message;
                }
            }

            return User;
        }


        public string UpdateUser()
        {
            string imgLocation = "";
            string results = "";
            string updateUser = string.Empty;
            string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    var addUserSP = "UpdateUser";
                    using (SqlCommand updateUserCommand = new SqlCommand(addUserSP, conn))
                    {
                        updateUserCommand.CommandType = CommandType.StoredProcedure;
                        updateUserCommand.Parameters.AddWithValue("@EmpID", this.EmpID);
                        updateUserCommand.Parameters.AddWithValue("@FirstName", this.FirstName);
                        updateUserCommand.Parameters.AddWithValue("@LastName", this.LastName);
                        updateUserCommand.Parameters.AddWithValue("@UserName", this.UserName);
                        updateUserCommand.Parameters.AddWithValue("@Password", this.Password);
                        updateUserCommand.Parameters.AddWithValue("@UserDisabled", this.UserDisabled);

                        if (PhotoID != null) //not sure about the object img; should be PhotoID?
                        {
                            byte[] img = null;
                            FileStream fs = new FileStream(imgLocation, FileMode.Open, FileAccess.Read);
                            BinaryReader br = new BinaryReader(fs);
                            img = br.ReadBytes((int)fs.Length);
                            updateUserCommand.Parameters.Add(new SqlParameter("@PhotoID", this.PhotoID));
                        }
                        else
                        {
                            PhotoID = null;
                        }
                        int x = updateUserCommand.ExecuteNonQuery();

                        //LogTransaction(int empID, string appmsg, string exceptmsg, string severity)

                        results = FirstName + " " + LastName + " has been updated.";
                    }
                }
                catch (SqlException sqlEx)
                {
                    results = sqlEx.Message;
                }
                catch (Exception ex)
                {
                    results = ex.Message;
                }
            }

            return results;

        }

        public string DeleteUser()
        {
            string imgLocation = "";
            string results = "";
            string addUser = string.Empty;
            string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    var addUserSP = "DeleteUser";
                    using (SqlCommand deleteUserCommand = new SqlCommand(addUserSP, conn))
                    {
                        deleteUserCommand.CommandType = CommandType.StoredProcedure;
                        deleteUserCommand.Parameters.AddWithValue("@EmpID", this.EmpID);
                        deleteUserCommand.Parameters.AddWithValue("@FirstName", this.FirstName);
                        deleteUserCommand.Parameters.AddWithValue("@LastName", this.LastName);
                        deleteUserCommand.Parameters.AddWithValue("@UserName", this.UserName);
                        deleteUserCommand.Parameters.AddWithValue("@Password", this.Password);
                        deleteUserCommand.Parameters.AddWithValue("@UserDisabled", this.UserDisabled);

                        byte[] img = null;
                        FileStream fs = new FileStream(imgLocation, FileMode.Open, FileAccess.Read);
                        BinaryReader br = new BinaryReader(fs);
                        img = br.ReadBytes((int)fs.Length);
                        deleteUserCommand.Parameters.Remove(new SqlParameter("@PhotoID", this.PhotoID));    // .Remove ???
                        int x = deleteUserCommand.ExecuteNonQuery();

                        //LogTransaction(int empID, string appmsg, string exceptmsg, string severity)

                        results = FirstName + " " + LastName + " has been deleted.";
                    }
                }
                catch (SqlException sqlEx)
                {
                    results = sqlEx.Message;
                }
                catch (Exception ex)
                {
                    results = ex.Message;
                }
            }

            return results;
        }


        public static User SearchUserByName(string un)
        {
            String connectionString = ConfigurationManager.ConnectionStrings["User"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);

            string result = "";
            User aUser = new User();

            try
            {
                string select = "SELECT * FROM [ConfigurationManager].[dbo].[Group] WHERE UserName ='" + un + "'";
                connection.Open();
                SqlCommand SearchCommand = new SqlCommand(select, connection);
                SqlDataReader reader = SearchCommand.ExecuteReader();
                while (reader.Read())
                {
                    aUser.EmpID = Convert.ToInt32(reader[0]);
                    aUser.FirstName = reader[1].ToString();
                    aUser.LastName = reader[2].ToString();
                    aUser.UserName = reader[3].ToString();
                    aUser.UserDisabled = Convert.ToBoolean(reader[4]);
                }
                reader.Close();
                connection.Close();
                return aUser;

            }
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            aUser.UserName = result;
            return aUser;
        }
    }
}






